package com.almori.hr.timetracker;

import java.sql.*;
import java.io.*;
import java.util.*;
import java.text.*;
import org.apache.commons.dbutils.DbUtils;

/**
 * Communication Oracle-Java Objects and gets the Connection data from a
 * properties file Nov 26, 2012, 1:45:00 PM
 *
 * @author gcastillo, nbenavidez
 */
public class TimeTrackerDAO {

    private static final String PROP_FILE = "DBConfig.properties";

    public static void main(String[] args) throws SQLException, java.lang.Exception {
        // Testing Method 
    }

    public void punchTimeCard(int id, String status, String reason) throws SQLException, Exception {
        if (status.equals("IN")) {
            boolean executeResult = false;
            Connection conn = getConnection();
            conn.setAutoCommit(true);
            Statement st = conn.createStatement();
            st = conn.createStatement();
            String sql = "update  timecards set log_out_time = sysdate,log_out_reason='" + reason + "' where log_in_time = (select  max(log_in_time) from    timecards where   id_empl =" + id + ") and id_empl=" + id;
            executeResult = st.execute(sql);
            String sql2 = "update  Employees set STATUS = 'OUT' where  id=" + id;
            executeResult = st.execute(sql2);
            DbUtils.closeQuietly(conn, st, null);
        } else {
            boolean executeResult = false;
            Connection conn = getConnection();
            conn.setAutoCommit(true);
            Statement st = conn.createStatement();
            st = conn.createStatement();
            String sql = "insert  into timecards (id_empl, log_in_time, log_out_time, total_hours) values (" + id + ", sysdate, null, null)";
            executeResult = st.execute(sql);
            String sql2 = "update  Employees set STATUS = 'IN' where  id =" + id;
            executeResult = st.execute(sql2);
            DbUtils.closeQuietly(conn, st, null);
        }

    }

    public void fixTimeCard(Timecard timeCard) throws Exception {
        boolean executeResult = false;
        Connection con = getConnection();
        con.setAutoCommit(true);
        java.sql.Timestamp sqlInDate = new java.sql.Timestamp(timeCard.getIn().getTime());
        java.sql.Timestamp sqlOutDate = new java.sql.Timestamp(timeCard.getOut().getTime());
        String sql = "update    timecards "
                + "set       log_in_time = ?, "
                + "          log_out_time = ?, "
                + "          log_out_reason = ? "
                + "where     id = ?";
        PreparedStatement stm = con.prepareStatement(sql);
        stm.setTimestamp(1, sqlInDate);
        stm.setTimestamp(2, sqlOutDate);
        stm.setString(3, timeCard.getReason());
        stm.setInt(4, timeCard.getId());
        stm.executeUpdate();
        DbUtils.closeQuietly(con, stm, null);
    }

    public void getTimeCards(int id_empl) throws SQLException, Exception {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();

        ResultSet rs = st.executeQuery("SELECT * FROM timecards where id_empl=" + id_empl + " order by id");
        //print results
        try {
            while (rs.next()) {
                System.out.println(rs.getInt("id") + " " + rs.getString("Id_empl"));
            }
        } catch (Exception e) {
        }
        DbUtils.closeQuietly(conn, st, rs);
    }

    public Employee getEmployeeByCedAndPassword(String CED, String PASS) throws SQLException, Exception {
        Employee aEmployee = new Employee();
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();

        ResultSet rs = st.executeQuery("SELECT * FROM EMPLOYEES WHERE CED='" + CED + "' AND PASS='" + PASS + "'");

        if (!rs.next()) {
            return null;
        } //No user found.
        else {
            aEmployee.setId(rs.getInt("ID"));
            aEmployee.setfName(rs.getString("FIRSTNAME"));
            aEmployee.setsName(rs.getString("SECONDNAME"));
            aEmployee.setlName(rs.getString("LASTNAME"));
            aEmployee.setStatus(rs.getString("STATUS"));
            aEmployee.setId_dept(rs.getInt("ID_DEPT"));
            aEmployee.setPic(rs.getString("PIC"));
            aEmployee.setCed(rs.getString("CED"));
            aEmployee.setRole(rs.getInt("ROLE_ID"));
            // everything but pass
            DbUtils.closeQuietly(conn, st, rs);
            return aEmployee;
        }
    }

    public Employee getEmployeeById(int id) throws SQLException, Exception {
        Employee aEmployee = new Employee();
        Department dep = new Department();
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();
        String sql = "SELECT   EMP.ID, EMP.FIRSTNAME, EMP.SECONDNAME, EMP.LASTNAME, EMP.STATUS, EMP.ID_DEPT, "
                + "         EMP.PIC, EMP.CED, EMP.ROLE_ID, EMP.LOCKED, DEP.ID DEP_ID, DEP.DESCRIPTION DEP_DESCRIPTION "
                + "FROM     EMPLOYEES EMP "
                + "INNER    JOIN DEPARTMENTS DEP ON (DEP.ID = EMP.ID_DEPT) "
                + "WHERE    EMP.ID=" + id;
        ResultSet rs = st.executeQuery(sql);

        if (!rs.next()) {
            return null;
        } else {
            aEmployee.setId(rs.getInt("ID"));
            aEmployee.setfName(rs.getString("FIRSTNAME"));
            aEmployee.setsName(rs.getString("SECONDNAME"));
            aEmployee.setlName(rs.getString("LASTNAME"));
            aEmployee.setStatus(rs.getString("STATUS"));
            aEmployee.setId_dept(rs.getInt("ID_DEPT"));
            aEmployee.setPic(rs.getString("PIC"));
            aEmployee.setCed(rs.getString("CED"));
            aEmployee.setRole(rs.getInt("ROLE_ID"));
            aEmployee.setLock(rs.getInt("LOCKED"));

            dep.setId(rs.getLong("DEP_ID"));
            dep.setDescription(rs.getString("DEP_DESCRIPTION"));

            aEmployee.setDepartment(dep);
            DbUtils.closeQuietly(conn, st, rs);
            return aEmployee;
        }
    }

    public List<Department> getDepartmentList() throws Exception {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();

        ResultSet rs = st.executeQuery("Select * from departments");
        List<Department> answer = new ArrayList();

        try {
            while (rs.next()) {
                Department temp = new Department(rs.getLong("ID"), rs.getString("CODE"), rs.getString("INITIALS"), rs.getString("Description"));
                answer.add(temp);
            }
        } catch (Exception e) {
        }
        DbUtils.closeQuietly(conn, st, rs);
        return answer;
    }

    public List<Department> findDepartments(String departmentCode, String departmentInitials, String departmentName) throws Exception {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();
        departmentCode = (departmentCode == null ? "" : departmentCode);
        departmentInitials = (departmentInitials == null ? "" : departmentInitials);
        departmentName = (departmentName == null ? "" : departmentName);

        ResultSet rs = st.executeQuery("select  * from departments "
                + "where   code like '%" + departmentCode + "%'"
                + "and     initials like '%" + departmentInitials + "%'"
                + "and     description like '%" + departmentName + "%'");
        List<Department> departments = new ArrayList();

        try {
            while (rs.next()) {
                Department iterDepartment = new Department(rs.getLong("ID"), rs.getString("CODE"), rs.getString("INITIALS"), rs.getString("Description"));
                departments.add(iterDepartment);
            }
        } catch (Exception e) {
        }
        DbUtils.closeQuietly(conn, st, rs);
        return departments;
    }

    public List<Employee> getEmployeeListByDept(int id) throws Exception {
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();

        ResultSet rs = st.executeQuery("Select id from employees where id_dept= " + id);
        List<Employee> answer = new ArrayList();

        try {
            while (rs.next()) {
                Employee temp = this.getEmployeeById(rs.getInt("ID"));
                answer.add(temp);
            }
        } catch (Exception e) {
        }
        DbUtils.closeQuietly(conn, st, rs);
        return answer;
    }

    public String getDeptById(int id) throws SQLException, Exception {

        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();

        ResultSet rs = st.executeQuery("SELECT DESCRIPTION FROM DEPARTMENTS WHERE ID=" + id);

        if (!rs.next()) {
            return null;
        } //No user found.
        else {
            String answer = rs.getString("Description");

            // everything but pass
            DbUtils.closeQuietly(conn, st, rs);
            return answer;
        }
    }

    public List<Timecard> getDatesByEmplId(int id_empl) throws SQLException, Exception {

        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();

        // ResultSet rs = st.executeQuery("SELECT trunc(log_in_time) logInDate,to_char(log_in_time, 'HH24:MI') logInTime,to_char(log_out_time, 'HH24:MI') logOutTime, log_out_reason from timecards where id_empl=" + id);
        ResultSet rs = st.executeQuery("SELECT * from timecards where id_empl=" + id_empl + " order by LOG_IN_TIME DESC");

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");

        List<Timecard> answer = new ArrayList();
        try {
            while (rs.next()) {

                Timecard temp = new Timecard();
                temp.setId(rs.getInt("ID"));
                temp.setId_empl(rs.getInt("ID"));
                try {
                    temp.setIn(dateFormat.parse(rs.getString("LOG_IN_TIME")));
                    temp.setOut(dateFormat.parse(rs.getString("LOG_OUT_TIME")));
                    temp.setReason(rs.getString("LOG_OUT_REASON"));
                } catch (Exception e) {
                }
                answer.add(temp);

            }
        } catch (Exception e) {
        }
        DbUtils.closeQuietly(conn, st, rs);
        return answer;
    }

    public Timecard getTimecardById(int id) throws SQLException, Exception {
        Timecard timeCard = new Timecard();
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
        String sql = "select    id, id_empl, log_in_time, log_out_time, log_out_reason, FORMATDATETIME(log_in_time, 'hh') in_hrs, "
                + "          FORMATDATETIME(log_in_time, 'm') in_min, FORMATDATETIME(log_in_time, 'a') in_ampm, FORMATDATETIME(log_out_time, 'hh') out_hrs, "
                + "          FORMATDATETIME(log_out_time, 'm') out_min, FORMATDATETIME(log_out_time, 'a') out_ampm "
                + "from      timecards where id = " + id;
        ResultSet rs = st.executeQuery(sql);

        if (!rs.next()) {
            return null;
        } else {
            timeCard.setId(rs.getInt("ID"));
            timeCard.setId_empl(rs.getInt("ID_EMPL"));
            try {
                if (!(rs.getString("LOG_IN_TIME") == null || rs.getString("LOG_IN_TIME").equals("") || rs.getString("LOG_IN_TIME").isEmpty())) {
                    timeCard.setIn(dateFormat.parse(rs.getString("LOG_IN_TIME")));
                }
                if (!(rs.getString("LOG_OUT_TIME") == null || rs.getString("LOG_OUT_TIME").equals("") || rs.getString("LOG_OUT_TIME").isEmpty())) {
                    timeCard.setOut(dateFormat.parse(rs.getString("LOG_OUT_TIME")));
                }
                timeCard.setReason(rs.getString("LOG_OUT_REASON") == null ? "" : rs.getString("LOG_OUT_REASON"));
                timeCard.setIn_hrs(rs.getString("IN_HRS") == null ? "" : rs.getString("IN_HRS"));
                timeCard.setIn_min(rs.getString("IN_MIN") == null ? "" : rs.getString("IN_MIN"));
                timeCard.setIn_ampm(rs.getString("IN_AMPM") == null ? "" : rs.getString("IN_AMPM"));
                timeCard.setOut_hrs(rs.getString("OUT_HRS") == null ? "" : rs.getString("OUT_HRS"));
                timeCard.setOut_min(rs.getString("OUT_MIN") == null ? "" : rs.getString("OUT_MIN"));
                timeCard.setOut_ampm(rs.getString("OUT_AMPM") == null ? "" : rs.getString("OUT_AMPM"));
            } catch (Exception e) {
            }
            DbUtils.closeQuietly(conn, st, rs);
            return timeCard;
        }
    }

    public List<Timecard> getDatesByDate(String date1, int id_empl) throws SQLException, Exception {

        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        st = conn.createStatement();

        ResultSet rs = st.executeQuery("SELECT * from timecards where FORMATDATETIME(LOG_IN_TIME, 'yyyy-MM-dd') = PARSEDATETIME('" + date1 + "', 'yyyy-MM-dd') and id_empl=" + id_empl + " order by LOG_IN_TIME");

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");

        List<Timecard> answer = new ArrayList();
        try {
            while (rs.next()) {

                Timecard temp = new Timecard();
                temp.setId(rs.getInt("ID"));
                temp.setId_empl(id_empl);
                try {
                    temp.setIn(dateFormat.parse(rs.getString("LOG_IN_TIME")));
                    temp.setOut(dateFormat.parse(rs.getString("LOG_OUT_TIME")));
                    temp.setReason(rs.getString("LOG_OUT_REASON"));
                } catch (Exception e) {
                }
                answer.add(temp);

            }
        } catch (Exception e) {
        }
        DbUtils.closeQuietly(conn, st, rs);
        return answer;
    }

    public Connection getConnection() throws Exception {
        try {
//         InputStream is = TimeTrackerDAO.class.getResourceAsStream(PROP_FILE);  
//         Properties prop = new Properties();  
//         prop.load(is);  
//         String driver = prop.getProperty("driver");  
//         String url = prop.getProperty("url");  
//         String username = prop.getProperty("username"); 
//         String password = prop.getProperty("password");

//           String driver = "oracle.jdbc.driver.OracleDriver";  
//           String url = "jdbc:oracle:thin:@10.2.100.51:1521:XE";  
//           String username = "HR_TRACKER"; 
//           String password = "HR_TRACKER";
//            String driver = "oracle.jdbc.driver.OracleDriver";
//            String url = "jdbc:oracle:thin:@localhost:1521:xe";
//            String username = "admon";
//            String password = "admon";
            String driver = "org.h2.Driver";
            String url = "jdbc:h2:tcp://localhost:9092/~/db/timetracker";
            String username = "sa";
            String password = "sa";
//         is.close();  
            Class.forName(driver);
            return DriverManager.getConnection(url, username, password);
            /* code to use values read from the file*/
        } catch (Exception e) {
            System.out.println("Failed to read from " + PROP_FILE + " file. " + e);
            return null;
        }
    }

    public void unlock(int id_empl) throws SQLException, Exception {
        boolean executeResult = false;
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        String sql = "insert  into timecards (LOCKED) values (0) WHERE id=" + id_empl;
        executeResult = st.execute(sql);
        DbUtils.closeQuietly(conn, st, null);
    }

    public void saveDeparment(Department department) throws Exception {
        boolean executeResult = false;
        String sql = "";
        Connection conn = getConnection();
        conn.setAutoCommit(true);
        Statement st = conn.createStatement();
        if (department.getId() == null) {
            sql = "insert   into departments "
                    + "         (code, initials, description) "
                    + "values   ('" + department.getCode() + "', '" + department.getInitials() + "', '" + department.getDescription() + "')";
        } else {
            sql = "update   departments "
                    + "set      code = '" + department.getCode() + "', "
                    + "         initials = '" + department.getInitials() + "', "
                    + "         description = '" + department.getDescription() + "'"
                    + "where    id = " + department.getId();
        }
        executeResult = st.execute(sql);
        DbUtils.closeQuietly(conn, st, null);
    }
}
